import sqlite3 from 'sqlite3';
import path from 'path';
import { promisify } from 'util';

const dbPath = path.join(process.cwd(), 'data', 'database.sqlite');

class Database {
  private db: sqlite3.Database;
  private initialized = false;

  constructor() {
    this.db = new sqlite3.Database(dbPath);
  }

  async initialize() {
    if (this.initialized) return;

    const run = promisify(this.db.run.bind(this.db));
    
    try {
      // 创建用户表
      await run(`
        CREATE TABLE IF NOT EXISTS users (
          id TEXT PRIMARY KEY,
          username TEXT UNIQUE NOT NULL,
          email TEXT UNIQUE NOT NULL,
          password TEXT NOT NULL,
          role TEXT DEFAULT 'editor',
          name TEXT NOT NULL,
          avatar TEXT,
          is_active INTEGER DEFAULT 1,
          last_login_at TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建成员表
      await run(`
        CREATE TABLE IF NOT EXISTS members (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          role TEXT NOT NULL,
          title TEXT,
          email TEXT,
          phone TEXT,
          avatar TEXT,
          bio TEXT,
          research_areas TEXT,
          homepage TEXT,
          order_index INTEGER DEFAULT 0,
          is_active INTEGER DEFAULT 1,
          join_date TEXT,
          graduation_date TEXT,
          current_position TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建项目表
      await run(`
        CREATE TABLE IF NOT EXISTS projects (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          description TEXT NOT NULL,
          full_description TEXT,
          image TEXT,
          images TEXT,
          status TEXT DEFAULT 'ongoing',
          category TEXT NOT NULL,
          tags TEXT,
          start_date TEXT NOT NULL,
          end_date TEXT,
          members TEXT,
          achievements TEXT,
          links TEXT,
          order_index INTEGER DEFAULT 0,
          is_published INTEGER DEFAULT 1,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建新闻表
      await run(`
        CREATE TABLE IF NOT EXISTS news (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          summary TEXT NOT NULL,
          content TEXT NOT NULL,
          image TEXT,
          images TEXT,
          category TEXT DEFAULT 'news',
          tags TEXT,
          author TEXT NOT NULL,
          publish_date TEXT NOT NULL,
          is_published INTEGER DEFAULT 1,
          is_pinned INTEGER DEFAULT 0,
          view_count INTEGER DEFAULT 0,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);
      
      // 添加images字段（如果不存在）
      try {
        await run(`ALTER TABLE news ADD COLUMN images TEXT`);
      } catch (error) {
        // 字段可能已存在，忽略错误
      }

      // 创建招新表
      await run(`
        CREATE TABLE IF NOT EXISTS recruitments (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          description TEXT NOT NULL,
          requirements TEXT,
          responsibilities TEXT,
          positions INTEGER DEFAULT 1,
          deadline TEXT,
          contact_email TEXT NOT NULL,
          contact_phone TEXT,
          is_active INTEGER DEFAULT 1,
          category TEXT DEFAULT 'undergraduate',
          application_form TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建申请表
      await run(`
        CREATE TABLE IF NOT EXISTS applications (
          id TEXT PRIMARY KEY,
          recruitment_id TEXT NOT NULL,
          applicant_name TEXT NOT NULL,
          applicant_email TEXT NOT NULL,
          applicant_phone TEXT,
          form_data TEXT NOT NULL,
          attachments TEXT,
          status TEXT DEFAULT 'pending',
          notes TEXT,
          reviewed_by TEXT,
          reviewed_at TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (recruitment_id) REFERENCES recruitments (id)
        )
      `);

      // 创建媒体文件表
      await run(`
        CREATE TABLE IF NOT EXISTS media_files (
          id TEXT PRIMARY KEY,
          filename TEXT NOT NULL,
          original_name TEXT NOT NULL,
          path TEXT NOT NULL,
          url TEXT NOT NULL,
          mime_type TEXT NOT NULL,
          size INTEGER NOT NULL,
          alt TEXT,
          caption TEXT,
          category TEXT,
          uploaded_by TEXT,
          created_at TEXT DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建网站配置表
      await run(`
        CREATE TABLE IF NOT EXISTS site_config (
          id TEXT PRIMARY KEY DEFAULT 'main',
          title TEXT DEFAULT '机器人创新实验室',
          subtitle TEXT,
          description TEXT DEFAULT '专注于机器人技术研发与创新',
          logo TEXT,
          favicon TEXT,
          contact_info TEXT,
          theme TEXT,
          seo TEXT,
          navigation TEXT,
          footer TEXT,
          analytics TEXT,
          carousel_settings TEXT,
          updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 添加轮播设置字段（如果不存在）
      try {
        await run(`ALTER TABLE site_config ADD COLUMN carousel_settings TEXT`);
      } catch (error) {
        // 字段可能已存在，忽略错误
      }

      // 插入默认管理员用户
      const bcrypt = require('bcryptjs');
      const defaultPassword = await bcrypt.hash('admin123', 10);
      
      await new Promise((resolve, reject) => {
        this.db.run(`
          INSERT OR IGNORE INTO users (id, username, email, password, role, name)
          VALUES ('admin', 'admin', 'admin@lab.com', ?, 'admin', '管理员')
        `, [defaultPassword], function(err) {
          if (err) reject(err);
          else resolve(this);
        });
      });

      // 插入默认网站配置
      await run(`
        INSERT OR IGNORE INTO site_config (id, title, description, contact_info, theme, navigation, footer)
        VALUES (
          'main',
          '机器人创新实验室',
          '致力于机器人技术研发与创新，培养优秀的机器人工程师',
          '{"email": "contact@roboticslab.com", "phone": "+86-10-12345678", "address": "某某大学某某楼某某室"}',
          '{"primaryColor": "#3b82f6", "secondaryColor": "#64748b", "backgroundColor": "#ffffff", "textColor": "#1e293b"}',
          '[{"id": "home", "label": "首页", "href": "/"}, {"id": "about", "label": "关于我们", "href": "/about"}, {"id": "members", "label": "团队成员", "href": "/members"}, {"id": "projects", "label": "项目成果", "href": "/projects"}, {"id": "news", "label": "新闻动态", "href": "/news"}, {"id": "recruitment", "label": "加入我们", "href": "/recruitment"}]',
          '{"sections": [{"title": "快速链接", "links": [{"label": "关于我们", "href": "/about"}, {"label": "联系我们", "href": "/contact"}]}], "copyright": "© 2024 机器人创新实验室. All rights reserved."}'
        )
      `);

      this.initialized = true;
      console.log('数据库初始化完成');
    } catch (error) {
      console.error('数据库初始化失败:', error);
      throw error;
    }
  }

  async run(sql: string, params: any[] = []): Promise<sqlite3.RunResult> {
    await this.initialize();
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function(err) {
        if (err) reject(err);
        else resolve(this);
      });
    });
  }

  async get<T = any>(sql: string, params: any[] = []): Promise<T | undefined> {
    await this.initialize();
    return new Promise<T | undefined>((resolve, reject) => {
      this.db.get(sql, params, (err, row) => {
        if (err) reject(err);
        else resolve(row as T | undefined);
      });
    });
  }

  async all<T = any>(sql: string, params: any[] = []): Promise<T[]> {
    await this.initialize();
    return new Promise<T[]>((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve((rows || []) as T[]);
      });
    });
  }

  async close(): Promise<void> {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }
}

export const db = new Database();